﻿'Option Explicit On
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.Xml
Imports System.IO
'For Finding Shared Folders on a server
Imports System.Object
Imports System.Management


Public Class ConnectionStringForm
    '--------------------------------From Connection Wizard SLN-------------------------------------------------
    Dim cs As String = "" 'used to store the sql connection string
    Dim osb As String = "" 'used to store the oledb connection string
    Dim dbname As String = "" 'used o store the name of the database
    Dim noPassword As Boolean = False
    Dim goodCon As Boolean = False
    Dim strComputer

    'Public Class SQLHelpers
    Private Function GetSQLServerList() As String()
        Dim dse As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance
        Dim dt As DataTable = dse.GetDataSources()
        If dt.Rows.Count = 0 Then
            Return Nothing
        End If

        Dim SQLServers As String() = New String(dt.Rows.Count - 1) {}
        Dim f As Integer = -1
        For Each r As DataRow In dt.Rows
            Dim SQLServer As String = r("ServerName").ToString()
            Dim Instance As String = r("InstanceName").ToString()
            If Instance IsNot Nothing AndAlso Instance <> "" Then
                SQLServer += "\" + Instance
            End If
            SQLServers(System.Math.Max(System.Threading.Interlocked.Increment(f), f - 1)) = SQLServer
        Next
        Array.Sort(SQLServers)
        Return SQLServers
    End Function
    ''' -------------------------------------------------------------------------------------------------
    ''' Returns a list of user databases on the specified server instance 
    ''' 
    ''' Name of server and if applicable the instance name 
    ''' DEV10\SQLEXPRESS 
    ''' MYSQLSERVER 
    ''' 
    ''' true if SSPI should be used; otherwise the username and password must be specified. 
    ''' username of an SQL server user account 
    ''' password for the SQL account specified by the username above 
    ''' fixed string array containing the list of user databases 
    Private Function GetSQLDatabaseList(ByVal serverInstanceName As String, ByVal useWindowsAuthentication As Boolean, ByVal username As String, ByVal password As String) As String()
        Dim csb As New SqlConnectionStringBuilder()
        csb.DataSource = serverInstanceName 'cmbServers.Text 'cboSrcDB.Text
        csb.IntegratedSecurity = useWindowsAuthentication
        'csb.TrustServerCertificate = useWindowsAuthentication
        csb.InitialCatalog = "master"
        If Not useWindowsAuthentication Then
            csb.UserID = username
            csb.Password = password
        End If


        Dim conn As New SqlConnection(csb.ToString())


        ' The 'where name like 'f%_' will filter out just those databases begining with "F" or "f"
        Dim da As New SqlDataAdapter("Select name from sysdatabases ", conn)
        'removed the following from above query so that all databases would be shown
        '  where name like 'f%_'
        Try
            Dim dt As New DataTable("Databases")
            Dim rowsAffected As Integer = da.Fill(dt)
            If dt Is Nothing OrElse rowsAffected <= 0 Then
                Return Nothing
            End If

            Dim f As Integer = -1
            Dim databases As String() = New String(dt.Rows.Count - 1) {}
            For Each r As DataRow In dt.Rows
                databases(System.Math.Max(System.Threading.Interlocked.Increment(f), f - 1)) = r("name").ToString()
            Next
            da.Dispose()
            Array.Sort(databases)
            Return databases
        Catch ex As SqlException
            MessageBox.Show("Cannot obtain database list:" & vbLf + ex.Message, "Migration Tool", MessageBoxButtons.OK, MessageBoxIcon.[Error])
            Return Nothing
        Finally
            conn.Close()
        End Try
    End Function
    '----------------------------------End Connection Wizard SLN--------------------------------------------------

    Public Sub SaveConnectionStringSettings()
        'These variables are declared in the Module GlobalVar
        'SQL_DB_Username = TB_SQL_Username_Text.Text
        'SQL_DB_Password = TB_SQL_Password_Text.Text
        'SQL_DB_Name = cmbDatabases.Text
        'SQL_Server_Name = cmbServers.Text

        'Save connection string values

        If ChkWinAuth.Checked = True Then
            SQL_DB_Username = TB_SQL_Username_Text.Text
            SQL_DB_Password = TB_SQL_Password_Text.Text
            SQL_DB_Name = cmbDatabases.Text
            SQL_Server_Name = cmbServers.Text
            SQL_DB_WinAuth = True

            My.Settings.Setting_SQL_DB_Name = SQL_DB_Name
            My.Settings.Setting_SQL_DB_Password = SQL_DB_Password
            My.Settings.Setting_SQL_DB_Server = SQL_Server_Name
            My.Settings.Setting_SQL_DB_Username = SQL_DB_Username
            My.Settings.Setting_SQL_DB_WinAuth = True

            'Save Minitab path and Bulk Upload Folders path
            'Minitab_Path = TB_Minitab_Path.Text
            'Bulk_UpLoads_Folder = cmb_Bulk_Uploads_Folder.Text
            'My.Settings.Setting_Minitab_Path = Minitab_Path
            'My.Settings.Setting_Bulk_UpLoads_Folder = Bulk_UpLoads_Folder

            My.Settings.Save()
            'Application.Restart()
        End If

        If ChkWinAuth.Checked = False Then
            TB_SQL_Username_Text.Enabled = True
            TB_SQL_Password_Text.Enabled = True

            SQL_DB_Username = TB_SQL_Username_Text.Text
            SQL_DB_Password = TB_SQL_Password_Text.Text
            SQL_DB_Name = cmbDatabases.Text
            SQL_Server_Name = cmbServers.Text

            My.Settings.Setting_SQL_DB_Name = SQL_DB_Name
            My.Settings.Setting_SQL_DB_Password = SQL_DB_Password
            My.Settings.Setting_SQL_DB_Server = SQL_Server_Name
            My.Settings.Setting_SQL_DB_Username = SQL_DB_Username
            My.Settings.Setting_SQL_DB_WinAuth = False

            'Save Minitab path and Bulk Upload Folders path
            'Minitab_Path = TB_Minitab_Path.Text
            'Bulk_UpLoads_Folder = cmb_Bulk_Uploads_Folder.Text
            'My.Settings.Setting_Minitab_Path = Minitab_Path
            'My.Settings.Setting_Bulk_UpLoads_Folder = Bulk_UpLoads_Folder

            My.Settings.Save()
            'Application.Restart()
        End If

    End Sub

    Private Sub ButtonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonSave.Click
        'Run subroutine SaveConnectionStringSettings to save settings
        SaveConnectionStringSettings()

        'Query Database and display records to comboboxes
        If ((SQL_DB_Name = "") Or (SQL_Server_Name = "")) Then
            'Do nothing
        Else
            Try
                'Create security and esbd_main tables
                ESBD_Form.Create_Tables()
            Catch ex As Exception
                Dim conn As New SqlConnection(csb.ToString())
                MsgBox(ex.message)
                conn.Close()
            End Try
        End If

        Me.Close()
        'Application.Restart()
    End Sub

    Private Sub ConnectionStringForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'On Form Load, Load saved settings and get list of SQL servers
        My.Settings.Reload()
        cmbDatabases.Text = My.Settings.Setting_SQL_DB_Name
        cmbServers.Text = My.Settings.Setting_SQL_DB_Server
        TB_SQL_Password_Text.Text = My.Settings.Setting_SQL_DB_Password
        TB_SQL_Username_Text.Text = My.Settings.Setting_SQL_DB_Username
        ChkWinAuth.Checked = My.Settings.Setting_SQL_DB_WinAuth
        'cmb_Bulk_Uploads_Folder.Text = My.Settings.Setting_Bulk_UpLoads_Folder
        'TB_Minitab_Path.Text = My.Settings.Setting_Minitab_Path

        'Get a list of servers before the window loads so as to avoid the end user
        'thinking that nothing is happening
        Try
            cmbServers.Items.AddRange(GetSQLServerList())
        Catch ex As Exception
            Dim mymsg As String = "There was a problem retrieving information about sql servers on your computer or network" & vbCrLf & _
            "you may need to enter this information into the form manually"
            MessageBox.Show(mymsg, "Error retrieving SQL Server Instances", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

        ESBD_Form.Close_Splash()
    End Sub

    Private Sub connectionstringform_opened(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Shown
        'When Connectionstringform is opened and displayed, set the main form cursor to default arrow cursor
        ESBD_Form.Cursor = Cursors.Default
        'If ConnectionString form is being called from Search Results
        'If CSF_Called_From_SearchResults = 1 Then
        '    'Set Groupbox text to bold
        '    'GroupBox4.Font = New Font(GroupBox4.Font, FontStyle.Bold)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Red
        '    Label2.ForeColor = Color.Red
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Black
        '    Label2.ForeColor = Color.Black
        '    ESBD_Form.Pause(0.1)
        '    TB_Minitab_Path.BackColor = Color.Yellow
        '    Label2.ForeColor = Color.Red
        '    'Label2.Font = New Font(Label2.Font, FontStyle.Bold)
        'End If
    End Sub

    Private Sub connectionstringform_close(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.FormClosed
        'This relates to the following subroutine from the main form. When it runs, the arrow cursor shows on the main form as busy loading, even after connectionstringform is closed. This will set the arrow cursor back to default arrow once the connectionstringform is closed.
        'Private Sub MenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Menu_SQL_Conn.Click
        '    'This will open ConnectionStringForm and show that the arrow cursor as busy loading form 
        '    Me.Cursor = Cursors.AppStarting
        '    ConnectionStringForm.Show()
        'End Sub

        'Check if Security table has been created on this table already
        'If ((SQL_DB_Name = "") Or (SQL_Server_Name = "")) Then
        '    'Do Nothing
        'Else

        '    ESBD_Form.Create_Tables()
        'End If

        'When ConnectionStringFrom is closed, set the arrow cursor as default arrow
        ESBD_Form.Cursor = Cursors.Default
    End Sub

    Private Sub cmbDatabases_DropDown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDatabases.DropDown
        'When the dropdown arrow is clicked on the Database field
        If ChkWinAuth.Checked = False Then
            If ((TB_SQL_Username_Text.Text = "") Or (TB_SQL_Password_Text.Text = "") Or cmbServers.Text = "") Then
                MsgBox("Please fill in all fields")
            Else
                Try
                    cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, False, TB_SQL_Username_Text.Text, TB_SQL_Password_Text.Text))
                Catch ex As Exception
                    Dim mymsg As String = "Username or password incorrect."
                    MessageBox.Show(mymsg, "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End Try
            End If

        Else
            If (cmbServers.Text = "") Then
                MsgBox("Please select a SQL Server first.")
            Else
                Try
                    cmbDatabases.Items.AddRange(GetSQLDatabaseList(cmbServers.Text, True, TB_SQL_Username_Text.Text, TB_SQL_Password_Text.Text))
                Catch ex As Exception
                    Dim mymsg As String = "Username or password incorrect."
                    MessageBox.Show(mymsg, "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End Try
            End If
        End If
    End Sub

    Private Sub ChkWinAuth_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChkWinAuth.CheckedChanged
        'If ChkWinAuth checkbox is unchecked, then enable username/password
        If ChkWinAuth.Checked = True Then
            TB_SQL_Password_Text.Enabled = False
            TB_SQL_Username_Text.Enabled = False
            TB_SQL_Username_Text.Text = ""
            TB_SQL_Password_Text.Text = ""
            SQL_DB_WinAuth = True
        Else
            TB_SQL_Username_Text.Enabled = True
            TB_SQL_Password_Text.Enabled = True
            SQL_DB_WinAuth = False
        End If
    End Sub

    Private Sub BTN_Test_Conn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Test_Conn1.Click
        'Test Connection to the database
        SaveConnectionStringSettings()

        If ((SQL_DB_Name = "") Or (SQL_Server_Name = "")) Then
            MsgBox("Please enter valid database settings and try again")
        Else
            ESBD_Form.OpenSQLConnection()
            Dim conn As New SqlConnection(csb.ToString())
            Try
                Me.Cursor = Cursors.WaitCursor
                cmd.Connection = conn
                conn.Open()
                MsgBox("Connection Successful!")
                conn.Close()
                ''Query Database and display records to comboboxes
                'Try
                '    ESBD_Form.OpenSQLConnection()
                '    'Dim conn As New SqlConnection(csb.ToString())
                '    cmd.Connection = conn
                '    cmd.CommandText = "Select * from sys.tables"

                '    conn.Open()
                '    dr = cmd.ExecuteReader()
                '    Do While dr.Read()
                '        ESBD_Form.cmb_Select_Search_Product.Items.Add(dr.GetString(0))
                '        ESBD_Form.cmb_Select_Import_Product.Items.Add(dr.GetString(0))
                '        ESBD_Form.cmb_Delete_Product.Items.Add(dr.GetString(0))
                '    Loop
                '    dr.Close()
                '    conn.Close()
                'Catch ex As Exception
                '    'Dim conn As New SqlConnection(csb.ToString())
                '    MsgBox(ex.message)
                '    conn.Close()
                'End Try
                Me.Cursor = Cursors.Default
            Catch ex As Exception
                MsgBox(ex.message + vbCrLf + vbCrLf + "Connection Failed")
                conn.Close()
                Me.Cursor = Cursors.Default
            End Try
        End If

    End Sub

    Private Sub BTN_Browse_Minitab_Path_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BTN_Browse_Minitab_Path.Click


        '        Dim FD_Filename As String = ""

        '        'Display file open dialog to let user choose minitab executable file
        '        If TB_Minitab_Path.Text = "" Then
        '            ESBD_Form.OpenFD.FileName = ""
        '            'FD_Filename = ESBD_Form.OpenFD.FileName
        '        Else
        '            ESBD_Form.OpenFD.FileName = TB_Minitab_Path.Text
        '        End If
        '        ESBD_Form.OpenFD.Filter = "Executable Files|*.exe|All Files|*.*"
        '        Dim FD_Result As Integer = ESBD_Form.OpenFD.ShowDialog()

        '        If FD_Result = DialogResult.Cancel Then
        '            'If FD_Filename = "Mtb.exe" Then
        '            'TB_Minitab_Path.Text = ""
        '            'End If
        '            GoTo Finish
        '        End If

        '        'Assigns Full path and name of file to the variable LimitFile
        '        Minitab_Path = ESBD_Form.OpenFD.FileName

        '        'Verify file is minitab executable
        '        'Dim Minitab_File_Path As String() 'String() is a string array
        '        'Dim result As Integer = 0


        '        'Minitab_File_Path = Minitab_Path.Split(New Char() {"\"c}) 'New Char() is an array. Here we are saying the string array words, will hold each piece of the labview filepath string that will be split by the \ character

        '        'Dim Minitab_File_Path_Segment As String 'Holds a string that is between \ char from the filepath
        '        'For Each Minitab_File_Path_Segment In Minitab_File_Path 'This can be thought of as for each sub-string in the labview filepath that was split up

        '        'Dim Position1 As Integer
        '        'Dim Search_String1 As String = "mtb.exe"

        '        'Position1 = InStr(Minitab_File_Path_Segment.ToUpper, Search_String1.ToUpper)

        '        'If (Position1 <> 0) Then
        '        '    GoTo SAVE_PATH
        '        'End If
        '        'Next 'go to next sub-string

        '        'MsgBox("File is not a Minitab executable file." + vbCrLf + vbCrLf + "Please select a Minitab executable (mtb.exe).", MsgBoxStyle.Information)
        '        'GoTo Finish

        'SAVE_PATH:
        '        TB_Minitab_Path.Text = Minitab_Path
        '        My.Settings.Setting_Minitab_Path = Minitab_Path
        '        My.Settings.Save()
        '        'MsgBox("Path to Minitab saved.", MsgBoxStyle.ApplicationModal)

        'Finish:
    End Sub
    Private Sub cmb_Bulk_Uploads_Folder_Dropdown(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_Bulk_Uploads_Folder.DropDown

        '        'Get Server Name
        '        Dim ServerName As String = ""
        '        ServerName = cmbServers.Text

        '        If ServerName = "" Then
        '            MsgBox("Please select an SQL Server first")
        '            GoTo Finish
        '        Else
        '            Dim ServerNameArray As String()
        '            ServerNameArray = ServerName.Split("\")
        '            ServerName = ServerNameArray(0)

        '            'MsgBox(ServerName)

        '            'Get UNC paths of the exported shares
        '            Dim UNC_Path As String = ""

        '            Using computer As New ManagementClass(ServerName)
        '                Using exportedShares As New ManagementClass("Win32_Share")


        '                    Dim localSystem As String = ServerName
        '                    'Dim localComputer As ManagementObjectCollection = computer.GetInstances() 'List of all servers
        '                    'Dim localComputer As ManagementObjectCollection = computer.GetInstances()

        '                    'For Each mo As ManagementObject In localComputer 'for each server in list of all servers
        '                    '    localSystem = mo("Name").ToString()
        '                    'Next

        '                    'If localSystem = ServerName Then
        '                    Dim shares As ManagementObjectCollection = exportedShares.GetInstances() 'List of Shared Folders

        '                    For Each share As ManagementObject In shares 'for each shared folder in the collection
        '                        'Console.WriteLine("UNC path \\{0}\{1}", localSystem, share("Name"))
        '                        UNC_Path = "\\" + localSystem + "\" + share("Name")
        '                        'MsgBox(UNC_Path)
        '                    Next
        '                    'End If


        '                End Using
        '            End Using
        '        End If

        '        '================================================================================================

        '        'using(ManagementClass exportedShares = new ManagementClass("Win32_Share" ))
        '        'using(ManagementClass computer = new ManagementClass("Win32_computersystem" ))
        '        'End Using
        '        'End Using
        '        'Dim localsystem As String = vbNull
        '        ''string localSystem = null;
        '        'ManagementObjectCollection localComputer = computer.GetInstances();
        '        'foreach(ManagementObject mo in localComputer)
        '        '{
        '        'localSystem = mo["Name"].ToString();
        '        '}
        '        'ManagementObjectCollection shares = exportedShares.GetInstances();
        '        'foreach(ManagementObject share in shares)
        '        '// dump UNC path
        '        'Console.WriteLine(@"UNC path \\{0}\{1}", localSystem, share["Name"]);
        '        '}
        'Finish:
    End Sub
End Class